import os

# import pymysql
from PyQt5 import QtGui, QtCore, QtWidgets, QtSql
import sys


from PyQt5.QtWidgets import QApplication, QMainWindow


class MainUi(QtWidgets.QMainWindow):

    def __init__(self):
        super().__init__()
        self.initUi()

    # 初始化UI界面
    def initUi(self):
        # 设置窗口标题
        self.setWindowTitle("州的先生 - 在PyQt5中使用数据库")
        # 设置窗口大小
        self.resize(600, 400)

        # 创建一个窗口部件
        self.widget = QtWidgets.QWidget()

        # 创建一个网格布局
        self.grid_layout = QtWidgets.QGridLayout()

        # 设置窗口部件的布局为网格布局
        self.widget.setLayout(self.grid_layout)

        # 创建一个按钮组
        self.group_box = QtWidgets.QGroupBox('数据库按钮')
        self.group_box_layout = QtWidgets.QVBoxLayout()
        self.group_box.setLayout(self.group_box_layout)

        # 创建一个表格部件
        self.table_widget = QtWidgets.QTableView()
        # 将上述两个部件添加到网格布局中
        self.grid_layout.addWidget(self.group_box, 0, 0)
        self.grid_layout.addWidget(self.table_widget, 0, 1)

        # 创建按钮组的按钮
        self.b_create_db = QtWidgets.QPushButton("创建数据库")
        self.b_create_db.clicked.connect(self.create_db)    #创建的按钮信号与槽函数相连接

        self.b_view_data = QtWidgets.QPushButton("浏览数据")
        self.b_view_data.clicked.connect(self.view_data)

        self.b_add_row = QtWidgets.QPushButton("添加一行")
        self.b_add_row.clicked.connect(self.add_row_data)

        self.b_delete_row = QtWidgets.QPushButton("删除一行")
        self.b_delete_row.clicked.connect(self.del_row_data)

        self.b_close = QtWidgets.QPushButton("退出")
        self.b_close.clicked.connect(self.close)

        # 添加按钮到按钮组中
        self.group_box_layout.addWidget(self.b_create_db)
        self.group_box_layout.addWidget(self.b_view_data)
        self.group_box_layout.addWidget(self.b_add_row)
        self.group_box_layout.addWidget(self.b_delete_row)
        self.group_box_layout.addWidget(self.b_close)

        # 设置UI界面的核心部件
        self.setCentralWidget(self.widget)
          #   MYSQL数据库
        # db = QtSql.QSqlDatabase.addDatabase('QMYSQL')
        # db.setHostName('主机名')
        # db.setDatabaseName('数据库名')
        # db.setUserName('用户名')
        # db.setPassword('密码')
        # db.open()

    # 创建数据库
    def create_db(self, **kwargs):
        try:
        # 调用输入框获取数据库名称
            if kwargs.get('db_name') == None:
                db_text, db_action = QtWidgets.QInputDialog.getText(self, '数据库名称', '请输入数据库名称', \
                                                                      QtWidgets.QLineEdit.Normal)
            else:
                db_text     = kwargs['db_name']
                db_action   = True
            if kwargs.get('table_name') == None:
                db_table = "scatter"
            else:
                db_table = kwargs['table_name']

            if (db_text.replace(' ', '') != '') and (db_action is True):
                print(db_text)
                self.db_name = db_text
                self.db_table_name = db_table
                self.db_list = []

                # 添加一个sqlite数据库连接并打开,db是数据库的句柄类, 注意第二个参数要设置
                self.db_list.append(QtSql.QSqlDatabase.addDatabase('QSQLITE', self.db_name))
                # self.db_list.append(QtSql.QSqlDatabase.addDatabase('QSQLITE'))
                self.db_list[0].setDatabaseName('{}.sqlite'.format(self.db_name))
                self.db_list[0].open()
                print('创建数据库{}成功，并已打开'.format(self.db_name + '.sqlite'))

                # 实例化一个查询对象
                query = QtSql.QSqlQuery(self.db_list[0])
                # 创建一个数据库表

                iFlag = query.exec_(f"create table if not exists {self.db_table_name}(ID integer PRIMARY KEY AUTOINCREMENT, "
                            "freq varchar(20), elev varchar(20), azim varchar(20), "
                            "rcs_vv varchar(20), rcs_hh varchar(20))")
                if iFlag:
                    print('在数据库中成功创建数据表{}'.format(self.db_table_name))
                # 插入数据
                iFlag = query.exec_(f"insert into {self.db_table_name}(freq, elev, azim, rcs_vv, rcs_hh) "
                                                    "values(8.0, 10.0, 0.0, 10.0, 2.0)")
                if iFlag:
                    print('成功插入初始数据')

                # db1.close()
                # db2.close()
        except Exception as e:
            print(e)

    # ------------浏览数据---------------
    def view_data(self, **kwargs):

        # QtSql.QSqlDatabase.addDatabase('QSQLITE', self.db_name)
        # QtSql.QSqlDatabase.database(self.db_name)
        # 实例化一个可编辑数据库表类
        self.db_list[0].open()
        self.model = QtSql.QSqlTableModel(db=self.db_list[0])
        self.table_widget.setModel(self.model)

        if kwargs.get('table_name') == None:
            table_name = 'scatter'
        else:
            table_name = kwargs['table_name']

        self.model.setTable(table_name)  # 设置数据模型的数据表
        self.model.setEditStrategy(QtSql.QSqlTableModel.OnFieldChange)  # 允许字段更改
        self.model.select()  # 查询所有数据

        # 设置表格头
        self.model.setHeaderData(0, QtCore.Qt.Horizontal, 'ID')
        self.model.setHeaderData(1, QtCore.Qt.Horizontal, 'Frequence')
        self.model.setHeaderData(2, QtCore.Qt.Horizontal, 'Elevation')
        self.model.setHeaderData(3, QtCore.Qt.Horizontal, 'Azimuth')
        self.model.setHeaderData(4, QtCore.Qt.Horizontal, 'RCS_VV')
        self.model.setHeaderData(5, QtCore.Qt.Horizontal, 'RCS_HH')

    #------------ 添加一行数据行---------------
    def add_row_data(self):
        # 如果存在实例化的数据模型对象
        if self.model:
            self.model.insertRows(self.model.rowCount(), 1)
        else:
            self.create_db()

    # 删除一行数据
    def del_row_data(self):
        if self.model:
            self.model.removeRow(self.table_widget.currentIndex().row())
        else:
            self.create_db()

if __name__ == "__main__":
    app = QApplication(sys.argv)
    window = MainUi()
    window.show()
    sys.exit(app.exec_())


